<?php

class model_wishProduct extends L {

    /**
     * 描述 ：分页的查询语句拼接
     * 作者 ：Zoro.Zhu
    **/
    public static function searchSql($params = array()){
        $sql = "SELECT
             `id`, `account`, `uploadTime`, `updTime`, `parentSku`, 
             `numberSaves`, `numberSold`, `reviewStatus`, `isPromoted`, `removedByMerchant`, 
             `data`, `totalImpressions`, `sum_ratings`, `total_ratings`, `rating_breakdown`, 
             `totalRefund`, `add_to_cart`, `totalFeedImpressions`
        FROM
            `wish_product`
        WHERE 1";
        $dxmAccount = model_com::getAccount('wish');
        $sql .= " AND `account` IN ('" . join("','", $dxmAccount) . "')";
        $sql .= !empty($params['search']['id']) ? " AND `id` = '{$params['search']['id']}' " : "";
        $sql .= !empty($params['search']['account']) ? "AND `account` IN ('" . join("','", $params['search']['account']) . "') " : "";
        $sql .= !empty($params['search']['uploadTimeStart']) ? " AND `uploadTime` >= '{$params['search']['uploadTimeStart']}' " : "";
        $sql .= !empty($params['search']['uploadTimeEnd']) ? " AND `uploadTime` <= '{$params['search']['uploadTimeEnd']}' " : "";
        $sql .= !empty($params['search']['updTimeStart']) ? " AND `updTime` >= '{$params['search']['updTimeStart']}' " : "";
        $sql .= !empty($params['search']['updTimeEnd']) ? " AND `updTime` <= '{$params['search']['updTimeEnd']}' " : "";
        $sql .= !empty($params['search']['parentSku']) ? " AND `parentSku` LIKE '%{$params['search']['parentSku']}%' " : "";
        $sql .= isset($params['search']['numberSavesStart']) && $params['search']['numberSavesStart'] !== '' ? " AND `numberSaves` >= '{$params['search']['numberSavesStart']}' " : "";
        $sql .= isset($params['search']['numberSavesEnd']) && $params['search']['numberSavesEnd'] !== '' ? " AND `numberSaves` <= '{$params['search']['numberSavesEnd']}' " : "";
        $sql .= isset($params['search']['numberSoldStart']) && $params['search']['numberSoldStart'] !== '' ? " AND `numberSold` >= '{$params['search']['numberSoldStart']}' " : "";
        $sql .= isset($params['search']['numberSoldEnd']) && $params['search']['numberSoldEnd'] !== '' ? " AND `numberSold` <= '{$params['search']['numberSoldEnd']}' " : "";
        $sql .= !empty($params['search']['reviewStatus']) ? " AND `reviewStatus` = '{$params['search']['reviewStatus']}' " : "";
        $sql .= isset($params['search']['isPromoted']) && $params['search']['isPromoted'] !== '' ? " AND `isPromoted` = '{$params['search']['isPromoted']}' " : "";
        $sql .= isset($params['search']['totalImpressionsStart']) && $params['search']['totalImpressionsStart'] !== '' ? " AND `totalImpressions` >= '{$params['search']['totalImpressionsStart']}' " : "";
        $sql .= isset($params['search']['totalImpressionsEnd']) && $params['search']['totalImpressionsEnd'] !== '' ? " AND `totalImpressions` <= '{$params['search']['totalImpressionsEnd']}' " : "";
        $sql .= isset($params['search']['sum_ratingsStart']) && $params['search']['sum_ratingsStart'] !== '' ? " AND `sum_ratings` >= '{$params['search']['sum_ratingsStart']}' " : "";
        $sql .= isset($params['search']['sum_ratingsEnd']) && $params['search']['sum_ratingsEnd'] !== '' ? " AND `sum_ratings` <= '{$params['search']['sum_ratingsEnd']}' " : "";
        $sql .= !empty($params['search']['statsDate']) ? " AND `id` IN ( SELECT id FROM wish_product_stats WHERE date = '{$params['search']['statsDate']}' )  " : "";
        if(!empty($params['search']['operDo'])){
            foreach($params['search']['operDo'] as $do){
                $sql .= " AND `oper`->>'$.{$do}' = 1";
            }
        }
        if(!empty($params['search']['operNotDo'])){
            foreach($params['search']['operNotDo'] as $do){
                $sql .= " AND `oper`->>'$.{$do}' is null ";
            }
        }
        return $sql;
    }


    /**
     * 描述 : 获取图表数据
     * 作者 : Zoro.Zhu
     */
    public static function getWishProductChart($params){
        $weekDate = !empty($params['timeS']) ? $params['timeS'] : date('Y-m-d', strtotime("-10 day"));
        $endTime = !empty($params['timeE']) ? $params['timeE'] : date('Y-m-d', strtotime("-2 day"));
        $sql = "SELECT
            `date`, `feed_impression`, `impressions`, `wishes`, `num_sold`,
            `add_to_cart`, `daily_sum_ratings`, `refund_reasons_dict_count`,
            `total_m_txn_count`, `total_m_txn_cost` 
        FROM
            wish_product_stats 
        WHERE
            `id` = '{$params['id']}' ";
        $sql .= " AND `date` >= '{$weekDate}' ";
        $sql .= " AND `date` <= '{$endTime}' ";
        $day = L::sql($sql);

        //赋值横坐标
        for ($i = strtotime($weekDate); $i <= strtotime($endTime); $i = $i + 86400) {
            $xAxis[] = date("Y-m-d", $i);
        }
        $data = [];
        foreach($day as $r){
            $data[$r['date']] = $r;
        }
        //赋值纵坐标
        foreach ($xAxis as $xAxisTime) {
            $series['feed_impression'][] = !empty($data[$xAxisTime]['feed_impression']) ? $data[$xAxisTime]['feed_impression'] : 0;
            $series['impressions'][] = !empty($data[$xAxisTime]['impressions']) ? $data[$xAxisTime]['impressions'] : 0;
            $series['wishes'][] = !empty($data[$xAxisTime]['wishes']) ? $data[$xAxisTime]['wishes'] : 0;
            $series['num_sold'][] = !empty($data[$xAxisTime]['num_sold']) ? $data[$xAxisTime]['num_sold'] : 0;
            $series['add_to_cart'][] = !empty($data[$xAxisTime]['add_to_cart']) ? $data[$xAxisTime]['add_to_cart'] : 0;
            $series['daily_sum_ratings'][] = !empty($data[$xAxisTime]['daily_sum_ratings']) ? $data[$xAxisTime]['daily_sum_ratings'] : 0;
            $series['refund_reasons_dict_count'][] = !empty($data[$xAxisTime]['refund_reasons_dict_count']) ? $data[$xAxisTime]['refund_reasons_dict_count'] : 0;
            $series['total_m_txn_count'][] = !empty($data[$xAxisTime]['total_m_txn_count']) ? $data[$xAxisTime]['total_m_txn_count'] : 0;
            $series['total_m_txn_cost'][] = !empty($data[$xAxisTime]['total_m_txn_cost']) ? $data[$xAxisTime]['total_m_txn_cost'] : 0;
            $series['operateInfo'][] = "\n";
        }
        $chart = [
            'title' => '产品统计数据图表',
            //纵坐标
            'legendData' => ['曝光量', '浏览量', '收藏量', '添加购物车', '扣库存', '当日售出数', '当日销售额$', '当日总评分', '当天退款数量'],
            //横坐标
            'xAxis' => $xAxis,
            'series' => [
                ["name" => '流量1', 'type' => 'line', 'data' => $series['feed_impression']],
                ["name" => '流量2', 'type' => 'line', 'data' => $series['impressions']],
                ["name" => '收藏量', 'type' => 'line', 'data' => $series['wishes']],
                ["name" => '添加购物车', 'type' => 'line', 'data' => $series['add_to_cart']],
                ["name" => '扣库存', 'type' => 'line', 'data' => $series['num_sold']],
                ["name" => '当日售出数', 'type' => 'line', 'data' => $series['total_m_txn_count']],
                ["name" => '当日销售额$', 'type' => 'line', 'data' => $series['total_m_txn_cost']],
                ["name" => '当日总评分', 'type' => 'line', 'data' => $series['daily_sum_ratings']],
                ["name" => '当天退款数量', 'type' => 'line', 'data' => $series['refund_reasons_dict_count']]
            ],
            'operateInfo' => $series['operateInfo'],
        ];
        return $chart;
    }

    /**
     * 描述 : 获取产品信息
     * 作者 : Zoro.Zhu
     */
    public static function getProductInfo($id){
        $sql = "SELECT
             `id`, `data`
        FROM
            `wish_product`
        WHERE 
            id = '{$id}'";
        $res = L::sql($sql);
        if(!empty($res[0]['data'])){
            return json_decode($res[0]['data'], true);
        }
        return [];
    }

    /**
     * 描述 : 获取产品的操作日志
     * 作者 : Zoro.Zhu
     */
    public static function getProductLog($params){
        $weekDate = !empty($params['timeS']) ? $params['timeS'] : '';
        $endTime = !empty($params['timeE']) ? $params['timeE'] : '';
        $sql = "SELECT
            `date`, remark 
        FROM
            `wish_product_log` 
        WHERE
            productId = '{$params['id']}' ";
        !empty($weekDate) && $sql .= " AND `date` >= '{$weekDate}' ";
        !empty($endTime) && $sql .= " AND `date` <= '{$endTime}' ";
        $sql .= " ORDER BY `date` DESC";
        return L::sql($sql);
    }

}